Design and Representation of the Time Dimension in Enterprise Data Warehouses - A Business Related Practical Approach

A data warehouse provides a consistent view of business data over time. In order to do that data is represented in logical dimensions, with time being one of the most important dimensions. Representing time, however, is not always straightforward due to the complex nature of time issues and the strong dependence of the time dimension on the type of business. This paper addresses the specific issues encountered during the design of the time dimension for multidimensional data warehouses. It introduces design and modeling techniques for representing time in the data warehouse by the use of one or multiple time dimensions or database timestamps. It also discusses generic problems linked to the design and implementation of the time dimension which have to be considered for (global) business processes, such as representing holidays and fiscal periods, increasing the granularity of business facts, considering the observation of daylight saving time and handling different time zones. These problems seem to have wide application, and yet, more in-depth investigations need to be conducted in this field for real-world timebased analysis in enterprise-wide data warehouses.